﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PM.Entity;
using System.Data;
using System.Data.OracleClient;

namespace PM.Services
{
    public class AccuredInterestServices
    {
        private static AccruedInterest GetAccruedInterestFromDataReader(IDataReader dataReader)
        {
            AccruedInterest retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new AccruedInterest();
                retVal.AccNo = dataReader["ACC_NO"].ToString();
                decimal interestAmount = 0.0M;
                decimal.TryParse(dataReader["INTEREST_AMOUNT"].ToString(), out interestAmount);
                retVal.InterestAmount = interestAmount;
                DateTime lastModDate = DateTime.MinValue;
                if (DateTime.TryParse(dataReader["LAST_MOD_DATE"].ToString(), out lastModDate))
                {
                    retVal.LastModifiedDate = lastModDate;
                }
                int posted = 0;
                int.TryParse(dataReader["POSTED"].ToString(), out posted);
                if (posted == 1)
                {
                    retVal.Posted = true;
                }
                else
                {
                    retVal.Posted = false;
                }

            }
            return retVal;
        }

        public static void CalculateAccruedInterest(string accNo, DateTime modDate)
        {

            decimal accruedInterest = 0.0M;
            DateTime lastModDate = DateTime.MinValue;
            bool newRecord = true;
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "select a.INTEREST_AMOUNT, a.LAST_MOD_DATE " +
                                   "from BANK_DEPOSIT t, ACCRUED_INTEREST a " +
                                   "where t.ACC_NO = a.ACC_NO " +
                                   "and a.ACC_NO = :v1 " +
                                   "and t.ACTIVE = 1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = accNo;

                cmd.Connection = con;
                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        newRecord = false;
                        decimal.TryParse(dataReader["INTEREST_AMOUNT"].ToString(), out accruedInterest);
                        DateTime.TryParse(dataReader["LAST_MOD_DATE"].ToString(), out lastModDate);
                    }                    
                }
                con.Close();

                BankDeposit bd = BankDepositServices.GetBankDepositByAccNo(accNo);
                DateTime today = DateTime.Today;
                decimal acctualYear = 365M;
                int noOfDays = 0;
                if (DateTime.IsLeapYear(today.Year))
                {
                    acctualYear = 366;
                }
                TimeSpan ts = TimeSpan.MinValue;
                if (!newRecord)
                {
                    ts = (modDate.Subtract(lastModDate));                    
                }
                else
                {
                    ts = (today.Subtract(bd.InvestDate));
                }
                noOfDays = ts.Days;
                if (noOfDays > 0)
                {
                    accruedInterest = accruedInterest + ((noOfDays/acctualYear) * (bd.Rate/100) * bd.InvestValue);
                    accruedInterest = Math.Round(accruedInterest, 2);

                    AccruedInterest ai = new AccruedInterest();
                    ai.AccNo = accNo;
                    ai.InterestAmount = accruedInterest;
                    ai.LastModifiedDate = modDate;
                    ai.Posted = false;
                    Add(ai);
                }

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static decimal GetAccruedInterestByKey(string accNo)
        {
            decimal accruedInterest = 0.0M;
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "select INTEREST_AMOUNT " +
                                   "from  ACCRUED_INTEREST " +
                                   "where ACC_NO = :v1 ";// +
                //"and   LAST_MOD_DATE = trunc(sysdate) ";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = accNo;

                cmd.Connection = con;
                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        decimal.TryParse(dataReader["INTEREST_AMOUNT"].ToString(), out accruedInterest);
                    }
                }
                return accruedInterest;

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
        public static void Add(AccruedInterest ai)
        {
            OracleConnection con = new OracleConnection();
            if (ai.LastModifiedDate == DateTime.MinValue)
            {
                ai.LastModifiedDate = DateTime.Today;
            }
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();

                cmd.CommandText = "select 1 from ACCRUED_INTEREST where ACC_NO = :v1 ";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = ai.AccNo;

                cmd.Connection = con;
                object obj = cmd.ExecuteScalar();
                if (obj != null && int.Parse(obj.ToString()) == 1)
                {
                    Update(ai);
                    return;
                }
                cmd.Parameters.Clear();
                cmd.CommandText = string.Empty;
                cmd.CommandText = "insert into ACCRUED_INTEREST(ACC_NO, INTEREST_AMOUNT, " +
                                                               "LAST_MOD_DATE, POSTED)";

                cmd.CommandText += " values(:v1,:v2,:v3,:v4)";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = ai.AccNo;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = ai.InterestAmount;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = ai.LastModifiedDate;
                if (ai.Posted)
                {
                    cmd.Parameters.Add(":v4", OracleType.Number).Value = 1;
                }
                else
                {
                    cmd.Parameters.Add(":v4", OracleType.Number).Value = 0;
                }
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        private static void Update(AccruedInterest ai)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update ACCRUED_INTEREST set " +
                                  "INTEREST_AMOUNT = :v2, " +
                                  "LAST_MOD_DATE = :v3, " +
                                  "POSTED = :v4 ";
                cmd.CommandText += " where ACC_NO = :v1";

                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = ai.AccNo;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = ai.InterestAmount;
                cmd.Parameters.Add(":v3", OracleType.DateTime).Value = ai.LastModifiedDate;

                if (ai.Posted)
                {
                    cmd.Parameters.Add(":v4", OracleType.Number).Value = 1;
                }
                else
                {
                    cmd.Parameters.Add(":v4", OracleType.Number).Value = 0;
                }
                cmd.Connection = con;
                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }
    }
}
